﻿using BookModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    //图书信息表
   
    public class BookInfoDAL
    {
        #region 查询所有图书信息－－外键查询
        public List<BookInfo> GetBookInfo()
        {
            string sql = @"select B.TName,A.Bname,A.BAthour,A.BMain,A.BPulish ,A.BPrice,A.BDate,C.Rname,A.Baddress from BookInfo A,TypeInfo B,ReadingInfo C
where A.RID=C.RID and A.TID=B.TID";
            List<BookInfo> LRI = new List<BookInfo>();
            if (SQLHelp.OpenConn())
            {
                SqlDataReader sdr = SQLHelp.ExecReader(sql,CommandType.Text);
                if (sdr != null)
                {
                   while(sdr.Read())
                   {
                        LRI.Add(new BookInfo
                        {
                            //BID = (int)item["BID"],
                            TName = sdr["TName"] as string,
                            Bname = sdr["Bname"] as string,
                            BAthour = sdr["BAthour"] as string,
                            BPulish = sdr["BPulish"] as string,
                            BMain = sdr["BMain"] as string,
                            BPrice = (decimal)sdr["BPrice"],
                            BDate = Convert.ToDateTime(sdr["BDate"]),
                            RName = sdr["RName"] as string,
                            Baddress = sdr["Baddress"] as string
                        });
                    }
                }
                SQLHelp.CloseConn();
            }
            return LRI;
        }
        #endregion

        #region 查询所有图书信息－－无外键查询
        public List<BookInfo> BookInfoAll()
        {
            string sql = "select *from BookInfo";
            List<BookInfo> LRI = new List<BookInfo>();
            if (SQLHelp.OpenConn())
            {
                SqlDataReader sdr = SQLHelp.ExecReader(sql, CommandType.Text);
                if (sdr != null)
                {
                    while (sdr.Read())
                    {
                        LRI.Add(new BookInfo
                        {
                            BID = (int)sdr["BID"],
                            Bname = sdr["Bname"] as string,
                            BAthour = sdr["BAthour"] as string,
                            BPulish = sdr["BPulish"] as string,
                            BMain = sdr["BMain"] as string,
                            BPrice = (decimal)sdr["BPrice"],
                            BDate = Convert.ToDateTime(sdr["BDate"]),
                            RID = sdr["RID"] as string,
                            Baddress = sdr["Baddress"] as string,
                            TID=(int)sdr["TID"]
                        });
                    }
                }
                SQLHelp.CloseConn();
            }
            return LRI;
        }
        #endregion
        #region 新书入库
        public string insertBookInfo(BookInfo bi)
        {
            string sql = @"insert into BookInfo values(@Bname,@BAthour,@BPulish,@BMain,@BPrice,@BDate,@RID,@Baddress,@TID)";

            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@Bname",bi.Bname),
                new SqlParameter("@BAthour",bi.BAthour),
                new SqlParameter("@BPulish",bi.BPulish),
                new SqlParameter("@BMain",bi.BMain),
                new SqlParameter("@BPrice",bi.BPrice),
                new SqlParameter("@BDate",bi.BDate),
                new SqlParameter("@RID",bi.RID),
                new SqlParameter("@Baddress",bi.Baddress),
                 new SqlParameter("@TID",bi.TID)
            };
            int count = SQLHelp.ExecQuery(sql,CommandType.Text,sp);
            switch (count)
            {
                case -2:
                    sql= "数据库连接出错,请联系你的管理员";
                    break;
                case -1:
                    sql= "数据输入出错,请检查你的输入";
                    break;
                case 0:
                    sql= "无任何添加";
                    break;
                default:
                    sql = "添加成功"+count+"条数据";
                    break;
            }
            return sql;
        }
        #endregion

        #region 修改信息
        public string UpdateBookInfo(BookInfo bi)
        {
            string sql = @"Update BookInfo set Bname=@Bname,BAthour=@BAthour,BPulish=@BPulish,BMain=@BMain,BPrice=@BPrice,BDate=@BDate,RID=@RID,Baddress=@Baddress,TID=@TID where BID=@BID";

            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@BID",bi.BID),
                new SqlParameter("@Bname",bi.Bname),
                new SqlParameter("@BAthour",bi.BAthour),
                new SqlParameter("@BPulish",bi.BPulish),
                new SqlParameter("@BMain",bi.BMain),
                new SqlParameter("@BPrice",bi.BPrice),
                new SqlParameter("@BDate",bi.BDate),
                new SqlParameter("@RID",bi.RID),
                new SqlParameter("@Baddress",bi.Baddress),
                 new SqlParameter("@TID",bi.TID)
            };
            int count = SQLHelp.ExecQuery(sql, CommandType.Text, sp);
            switch (count)
            {
                case -2:
                    sql= "数据库连接出错,请联系你的管理员";
                    break;
                case -1:
                    sql= "数据输入出错,请检查你的输入";
                    break;
                case 0:
                    sql= "无任何修改";
                    break;
                default:
                    sql = "修改成功"+count+"条数据";
                    break;
            }
            return sql;
        }
        #endregion
    }
}
